package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import pojo.Customer;
import pojo.Favourite;
import util.DBUtils;

public class CustomerDaoImpl implements CustomerDao {


	public int addCustomer(Customer customer) {
		String sql = "insert into customers(id,cname,sex,sal,birth) values(myseq.nextval,?,?,?,?)";
		PreparedStatement pst = null;
		ResultSet rs = null;
		PreparedStatement pst1 = null;
		int m = 0;
		Connection conn = DBUtils.createConnection();
		try {
			conn.setAutoCommit(false);
			String generatedColumns[] = { "ID" };  
			pst = conn.prepareStatement(sql,generatedColumns);
			pst.setString(1, customer.getCname());
			pst.setBoolean(2, customer.isSex());
			pst.setDouble(3, customer.getSal());
			pst.setDate(4, new java.sql.Date(customer.getBirth().getTime()));
			m = pst.executeUpdate();
			rs = pst.getGeneratedKeys();
			int key = -1;
			while (rs.next()) {
				key = rs.getInt(1);
			}
			String fsql = "insert into customer_fav values(?,?)";
			pst1 = conn.prepareStatement(fsql);
			for (Favourite fav:customer.getFavs()) {
				pst1.setInt(1, key);
				pst1.setInt(2, fav.getId());
				pst1.addBatch();
			}
			pst1.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				return -1;
			}
			e.printStackTrace();
			return -1;
		}finally{
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBUtils.close(null,pst1 , null);
			DBUtils.close(conn, pst, null);
		}
		return m;
	}

	public int deleteCustomer(Integer id) {
		String sql = "delete customers where id=?";
		Connection conn = DBUtils.createConnection();
		PreparedStatement pst = null;
		PreparedStatement fpst = null;
		int m = 0;
		try {
			conn.setAutoCommit(false);
			String fsql = "delete customer_fav where c_id=?";
			fpst = conn.prepareStatement(fsql);
			fpst.setInt(1, id);
			fpst.executeUpdate();
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			m = pst.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return -1;
		}finally{
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBUtils.close(conn, fpst, null);
			DBUtils.close(conn, pst, null);
		}
		return m;
	}

	public int updateCustomer(Customer customer) {
		String sql = "update customers set cname=?,sex=?,sal=?,birth=? where id =?";
		Connection conn = DBUtils.createConnection();
		PreparedStatement pst = null;
		PreparedStatement dfpst = null;
		PreparedStatement fpst = null;
		int m = 0;
		try {
			conn.setAutoCommit(false);
			pst = conn.prepareStatement(sql);
			pst.setString(1, customer.getCname());
			pst.setBoolean(2, customer.isSex());
			pst.setDouble(3, customer.getSal());
			pst.setDate(4, new java.sql.Date(customer.getBirth().getTime()));
			pst.setInt(5, customer.getId());
			m = pst.executeUpdate();
			List<Favourite> favourites = customer.getFavs();
			String dfsql =  "delete customer_fav where c_id=?";
			dfpst = conn.prepareStatement(dfsql);
			dfpst.setInt(1, customer.getId());
			dfpst.executeUpdate();
			String fsql = "insert into customer_fav values(?,?)";
			fpst = conn.prepareStatement(fsql);
			for (Favourite fav:favourites) {
				fpst.setInt(1, customer.getId());
				fpst.setInt(2, fav.getId());
				fpst.addBatch();
			}
			fpst.executeBatch();
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			return -1;
		}finally{
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBUtils.close(null, dfpst, null);
			DBUtils.close(null, fpst, null);
			DBUtils.close(conn, pst, null);
		}
		return m;
	}

	public Customer loadCustomer(Integer id) {
		String sql = "select * from customers where id=?";
		Connection conn = DBUtils.createConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		PreparedStatement fpst = null;
		ResultSet frs = null;
		Customer customer =null;
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if (rs.next()) {
				customer = new Customer();
				customer.setId(rs.getInt("id"));
				customer.setCname(rs.getString("cname"));
				customer.setSex(rs.getBoolean("sex"));
				customer.setSal(rs.getDouble("sal"));
				customer.setBirth(new Date(rs.getDate("birth").getTime()));
			}
			String faSql = "with c as(select * from customer_fav where c_id=?) select f.id id,f.fav fav from c left join favorites f on f.id=c.f_id";
			
			fpst = conn.prepareStatement(faSql);
			fpst.setInt(1, id);
			frs = fpst.executeQuery();
			List<Favourite> favourites = new ArrayList<>();
			while (frs.next()) {
				Favourite favourite = new Favourite();
				favourite.setId(frs.getInt("id"));
				favourite.setFav(frs.getString("fav"));
				favourites.add(favourite);
			}
			customer.setFavs(favourites);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtils.close(null,fpst ,frs);
			DBUtils.close(conn, pst, rs);
		}
		return customer;
	}

	public List<Customer> findAll() {
		List<Customer> customers = new ArrayList<Customer>();
		String sql = "select * from customers";
		Connection conn = DBUtils.createConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()) {
				Customer customer = new Customer();
				customer.setId(rs.getInt("id"));
				customer.setCname(rs.getString("cname"));
				customer.setSex(rs.getBoolean("sex"));
				customer.setSal(rs.getDouble("sal"));
				customer.setBirth(new Date(rs.getDate("birth").getTime()));
				customers.add(customer);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtils.close(conn, pst, rs);
		}
		return customers;
	}

}
